package day2;


import day1.Exer.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//处理s_subject相关的操作
public class SubjectDao {
    public int add(Subject s) {
        List<Object> list = new ArrayList<>();
        list.add(s.getId());
        list.add(s.getSubjectname());
        list.add(s.getClasshour());
        list.add(s.getGradeid());
        String sql = "insert into s_subject(id,subjectname,classhour,gradeid) values (?,?,?,?)";
        return DBUtil.executeUpdate(sql, list);
    }

    public int update(Subject s) {
        List<Object> list = new ArrayList<>();
        list.add(s.getId());
        list.add(s.getSubjectname());
        list.add(s.getClasshour());
        list.add(s.getGradeid());
        String sql = "update s_subject set subjectname=?,set classhour=?,set gradeid=? where id=" + s.getId();
        return DBUtil.executeUpdate(sql, list);
    }

    public int del(long id) {
        List<Object> list = new ArrayList<>();
        list.add(id);

        String sql = "Delete from s_subject where id=" + id;
        return DBUtil.executeUpdate(sql, list);
    }

    //主键找对象
    public Subject get(long id) {
        Connection connection = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        Subject s = null;//null可以解决找不到的问题
        try {
            connection = DBUtil.getConnection();
            String sql = "select id,subjectname,classhour,gradeid from s_subject where id=" + id;
            st = connection.prepareStatement(sql);
            rs = st.executeQuery();
            if (!rs.next()) {
                s.setId(id);
                s.setClasshour(rs.getInt("classhour"));
                s.setSubjectname(rs.getString("subjectname"));
                s.setGradeid(rs.getLong("gradeid"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, connection);
        }
        return s;
    }

    //查询全部对象
    public List<Subject> findALL() {
        List<Subject> subjectList = new ArrayList<>();
        Connection connection = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from s_subject order by id desc ";
            st = connection.prepareStatement(sql);
            rs = st.executeQuery();
            while (rs.next()) {
                Subject s = null;
                s.setId(rs.getLong("id"));
                s.setClasshour(rs.getInt("classhour"));
                s.setSubjectname(rs.getString("subjectname"));
                s.setGradeid(rs.getLong("gradeid"));
                subjectList.add(s);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, connection);
        }
        return subjectList;
    }


    public List<Subject> search(int pageNo, int pageSize) {
        List<Subject> subjectList = new ArrayList<>();
        Connection connection = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from s_subject order by id desc limit ?,?";
            System.out.println(sql);
            st = connection.prepareStatement(sql);
            st.setInt(1, (pageNo - 1) * pageSize);
            st.setInt(2, pageSize);
            rs = st.executeQuery();
            while (rs.next()) {
                subjectList.add(new Subject(rs.getLong("id"),rs.getString("subjectname"),rs.getInt("classhour"),rs.getLong("gradeid")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, connection);
        }
        return subjectList;
    }

}